package com.blue.waffle.iwc.database;

import java.io.File;
import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.badlogic.gdx.Gdx;
import com.blue.waffle.iwc.domains.Difficulty;
import com.blue.waffle.iwc.domains.helpers.DifficultyHelper;
import com.blue.waffle.iwc.domains.helpers.LevelHelper;
import com.blue.waffle.iwc.domains.helpers.WordHelper;

import android.content.Context;
import android.os.Handler;
import android.util.Log;

public class AndroidSqliteActionResolver implements SqliteActionResolver {

	private Handler uiThread;
	private Context appContext;
	private Connection connection;

	private static final String DATABASE_URI = "jdbc:sqldroid:";
	private static final String DATABASE_NAME = "IWC.db";
	private static final String WORD_LIST_FILE="data/wordlist.csv";
	private static final String CREATE_STATEMENT = "CREATE TABLE IF NOT EXISTS difficulty (id INTEGER PRIMARY KEY, name TEXT, description TEXT);"
			+ "CREATE TABLE IF NOT EXISTS achievement (id INTEGER PRIMARY KEY, description TEXT, picture TEXT, completed NUMERIC, title TEXT);"
			+ "CREATE TABLE IF NOT EXISTS difficulty_level (difficulty_id NUMERIC, level_id NUMERIC);"
			+ "CREATE TABLE IF NOT EXISTS level (word_id NUMERIC, id INTEGER PRIMARY KEY, title TEXT UNIQUE ON CONFLICT IGNORE,difficulty_id NUMERIC);"
			+ "CREATE TABLE IF NOT EXISTS profile (id INTEGER PRIMARY KEY, name TEXT, level_random_seed NUMERIC, last_played_level_index NUMERIC, current_difficulty NUMERIC, attempts NUMERIC);"
			+ "CREATE TABLE IF NOT EXISTS profile_achievement (id INTEGER PRIMARY KEY, profile_id , achievement_id , completed_at NUMERIC);"
			+ "CREATE TABLE IF NOT EXISTS profile_level (playtime NUMERIC, profile_id NUMERIC, level_id NUMERIC, score NUMERIC, time_completed NUMERIC);"
			+ "CREATE TABLE IF NOT EXISTS word (id INTEGER PRIMARY KEY, word TEXT UNIQUE ON CONFLICT IGNORE, picture TEXT, definition TEXT);";

	private static final String DROP_STATEMENT = "DROP TABLE IF EXISTS achievement;"
			+ "DROP TABLE IF EXISTS profile_achievement;"
			+ "DROP TABLE IF EXISTS difficulty;"
			+ "DROP TABLE IF EXISTS difficulty_level ;"
			+ "DROP TABLE IF EXISTS level ;"
			+ "DROP TABLE IF EXISTS profile ;"
			+ "DROP TABLE IF EXISTS profile_level ;"
			+ "DROP TABLE IF EXISTS word ;";

	public AndroidSqliteActionResolver(Context context) {
		this.setUiThread(new Handler());
		this.setAppContext(context);
		try {
			Class.forName("org.sqldroid.SQLDroidDriver").newInstance();
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public synchronized Connection getConnection() {
		try {

			File dbDir = new File(appContext.getDatabasePath(DATABASE_NAME)
					.getParent());
			if (!dbDir.exists()) {
				dbDir.mkdir();
			}
			connection = DriverManager.getConnection(DATABASE_URI
					+ appContext.getDatabasePath(DATABASE_NAME));
			return connection;
		} catch (SQLException e) {
			Log.e(e.getClass().getSimpleName(), e.getMessage());
		}
		return null;
	}

	public synchronized void init() {
		Statement st;
		try {
			getConnection();
			st = AndroidSqliteActionResolver.this.connection.createStatement();
			ResultSet rs = st
					.executeQuery("SELECT count(name) FROM sqlite_master WHERE type='table'");
			if (rs.next()) {
				if (rs.getInt(1) > 1) {
					System.out.println("Drop database!");
					String[] dropStatements = DROP_STATEMENT.split(";");
					for (String statement : dropStatements) {
						st.executeUpdate(statement);
					}
				}
			}
			rs.close();
			String[] createStatements = CREATE_STATEMENT.split(";");
			for (String statement : createStatements) {
				st.executeUpdate(statement);
			}
			st.close();
			
			DifficultyHelper difficultyHelper=new DifficultyHelper(connection);
			Difficulty easy=new Difficulty("Easy","Easy Difficulty");
			Difficulty normal=new Difficulty("Normal","Normal Difficulty");
			Difficulty hard=new Difficulty("Hard","Hard Difficulty");
			
			difficultyHelper.insert(easy);
			difficultyHelper.insert(normal);
			difficultyHelper.insert(hard);
			
			WordHelper wordHelper=new WordHelper(connection);
			try {
				Reader file=Gdx.files.internal(WORD_LIST_FILE).reader();
				wordHelper.insertWordsFromCSV(file);
			} catch (IOException e) {
				e.printStackTrace();
			}
			
			LevelHelper levelHelper=new LevelHelper(connection);
			levelHelper.insertLevels(wordHelper.selectAll(), difficultyHelper.selectAll());
			
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public Handler getUiThread() {
		return uiThread;
	}

	public void setUiThread(Handler uiThread) {
		this.uiThread = uiThread;
	}

	public Context getAppContext() {
		return appContext;
	}

	public void setAppContext(Context appContext) {
		this.appContext = appContext;
	}

}
